package net.huashitong.user.dao;

import com.sirdc.modules.core.dao.GenericQuery;
import com.sirdc.modules.core.dao.StringDao;
import com.sirdc.modules.utils.StringUtils;
import net.huashitong.Project.entity.NewProjects;
import net.huashitong.oversee.databean.DepartRank;
import net.huashitong.user.databean.BnpUserDatabean;
import net.huashitong.user.entity.BnpUser;
import net.huashitong.user.filter.BnpUserFilter;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.text.Collator;
import java.util.*;

@Repository
public class BnpUserDao extends StringDao<BnpUser> {

    /**
     * 用户查询
     *
     * @param filter
     * @return
     */
    public List<BnpUser> getUserInfo(BnpUserFilter filter) {
        GenericQuery query = createQuery("obj");
        if (filter.getName() != null && !"".equals(filter.getName())) {
            query.append(" and obj.name like:name").setParam("name", "%" + filter.getName() + "%");
        }
        if (filter.getBranch() != null && !"".equals(filter.getBranch())) {
            query.append(" and obj.branch =:branch").setParam("branch", filter.getBranch());
        }
        if (filter.getBranchType() != null && !"".equals(filter.getBranchType())) {
            query.append(" and obj.branchType =:branchType").setParam("branchType", filter.getBranchType());
        }
        if (filter.getRole() != null && !"".equals(filter.getRole())) {
            query.append(" and obj.role =:role").setParam("role", filter.getRole());
        }
        if (filter.getPhone() != null && !"".equals(filter.getPhone())) {
            query.append(" and obj.phone =:phone").setParam("phone", filter.getPhone());
        }
        if (filter.getGroupCode() != null && !"".equals(filter.getGroupCode())) {
            query.append(" and obj.groupCode =:groupCode").setParam("groupCode", filter.getGroupCode());
        }

        //模糊搜索
        if(StringUtils.isNotBlank(filter.getSearchWord())){
            query.append(" and (obj.name like:name or " +
                    "obj.branch like:branch or "+
                    "obj.role like:role or "+
                    "obj.groupCode like:groupCode or "+
                    "obj.phone like:phone) ").setParam("name",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("branch",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("role",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("groupCode",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("phone",
                    StringUtils.getLikewords(filter.getSearchWord()));
        }

        if (filter.getIsGroupCode() != null && !"".equals(filter.getIsGroupCode())) {
            if ("0".equals(filter.getIsGroupCode())) {
                //只查询督查组
                query.append(" and obj.groupCode is not null");
            }
        }
        query.append(" and LENGTH(obj.phone) >:length").setParam("length", 10);
        return query.listResult(filter);
    }


    /**
     * 用户查询2
     *
     * @param filter
     * @return
     */
    public List<BnpUser> queryUserInfo2(BnpUserFilter filter) {
        GenericQuery query = createQuery("obj");
        if (filter.getName() != null && !"".equals(filter.getName())) {
            query.append(" and obj.name like:name").setParam("name", "%" + filter.getName() + "%");
        }
        if (filter.getBranch() != null && !"".equals(filter.getBranch())) {
            query.append(" and obj.branch =:branch").setParam("branch", filter.getBranch());
        }
        if (filter.getBranchType() != null && !"".equals(filter.getBranchType())) {
            query.append(" and obj.branchType =:branchType").setParam("branchType", filter.getBranchType());
        }
        if (filter.getRole() != null && !"".equals(filter.getRole())) {
            query.append(" and obj.role =:role").setParam("role", filter.getRole());
        }
        if (filter.getPhone() != null && !"".equals(filter.getPhone())) {
            query.append(" and obj.phone =:phone").setParam("phone", filter.getPhone());
        }
        if (filter.getGroupCode() != null && !"".equals(filter.getGroupCode())) {
            query.append(" and obj.groupCode =:groupCode").setParam("groupCode", filter.getGroupCode());
        }
        query.append(" and LENGTH(obj.phone) >:length").setParam("length", 10);
        return query.listResult(filter);
    }

    public BnpUser getBnpUser(String username, String password) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.phone =:phone").setParam("phone", username);
        query.append(" and obj.password =:password").setParam("password", password);
        return query.firstResult();
    }

    public BnpUser getBnpUserByName(String sysId) {

        GenericQuery query = createQuery("obj");
        query.append(" and obj.sysId =:sysId").setParam("sysId", sysId);
        return query.firstResult();
    }

    //    /**
//     * 查询总的条数
//     * @return
//     */
//    public Integer getBnpUserSum(){
//        GenericQuery query = createQuery("obj");
//        return query.count();
//    }
//
    public Integer getBnpUserCount(BnpUserFilter filter) {
//        StringBuffer sql = new StringBuffer();
//        sql.append(" select COUNT(1) page FROM t_bnp_user WHERE 1=1");
//        if (filter.getName()!=null&&!"".equals(filter.getName()))
//        sql.append(" and name like %"+filter.getName()+"%");
//        if (filter.getPhone()!=null&&!"".equals(filter.getPhone()))
//            sql.append(" and phone ="+filter.getPhone());
//        SQLQuery query = getSession().createSQLQuery(sql.toString());
//        query.setResultTransformer(Transformers.aliasToBean(BnpUserFilter.class));
//        List<BnpUserFilter> list = (List<BnpUserFilter>) query.list();
//        return list.get(0);
        GenericQuery query = createQuery("obj");
//        if (filter.getName()!=null&&!"".equals(filter.getName()))
//        query.append(" and name like %"+filter.getName()+"%");
//        if (filter.getPhone()!=null&&!"".equals(filter.getPhone()))
//            query.append(" and phone ="+filter.getPhone());
        if (filter.getName() != null && !"".equals(filter.getName())) {
            query.append(" and obj.name like:name").setParam("name", "%" + filter.getName() + "%");
        }
        if (filter.getPhone() != null && !"".equals(filter.getPhone())) {
            query.append(" and obj.phone like:phone").setParam("phone", "%" + filter.getPhone() + "%");
        }
        if (filter.getBranch() != null && !"".equals(filter.getBranch())) {
            query.append(" and obj.branch =:branch").setParam("branch", filter.getBranch());
        }
        if (filter.getRole() != null && !"".equals(filter.getRole())) {
            query.append(" and obj.role =:role").setParam("role", filter.getRole());
        }
        return query.count();
    }

    public BnpUser getBnpUserByPhone(String phone) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.phone =:phone").setParam("phone", phone);
        return query.firstResult();
    }

    public BnpUser getBnpUserByPhoneAndName(String phone, String name) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.phone =:phone").setParam("phone", phone);
        query.append(" and obj.name =:name").setParam("name", name);
        return query.firstResult();
    }

    public List<BnpUser> queryByFilter(BnpUserFilter filter) {
        GenericQuery query = createQuery("obj");
        if (StringUtils.isNotBlank(filter.getName())) {
            query.append(" and obj.name like:name").setParam("name",
                    StringUtils.getLikewords(filter.getName()));
        }
        if (StringUtils.isNotBlank(filter.getPhone())) {
            query.append(" and obj.phone =:phone").setParam("phone", filter.getPhone());
        }
        if (StringUtils.isNotBlank(filter.getBranch())) {
            query.append(" and obj.branch =:branch").setParam("branch", filter.getBranch());
        }
        if (StringUtils.isNotBlank(filter.getBranchType())) {
            query.append(" and obj.branchType =:branchType").setParam("branchType", filter.getBranchType());
        }
        if (StringUtils.isNotBlank(filter.getGroupCode())) {
            query.append(" and obj.groupCode =:groupCode").setParam("groupCode", filter.getGroupCode());
        }
        if (StringUtils.isNotBlank(filter.getRole())) {
            query.append(" and obj.role =:role").setParam("role", filter.getRole());
        }
        query.append(" ORDER BY sysId+0 desc");
        return query.listResult();
    }

    public List<String> queryAllBranchs() {
        List<String> resList = new ArrayList<>();
        StringBuffer sql = new StringBuffer();
        sql.append("select distinct branch,branch_type branchType from t_bnp_user order by branch_type+0 desc");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(BnpUser.class));
        List<BnpUser> list = query.list();
        for (BnpUser user : list) {
            resList.add(user.getBranch());
        }
        return resList;
    }

    public List<BnpUser> queryBranchsByType(BnpUserFilter filter) {
        GenericQuery query = createQuery("obj");
        if (StringUtils.isNotBlank(filter.getBranchType())) {
            query.append(" and obj.branchType = "+filter.getBranchType() );
        }
        //模糊搜索
        if(StringUtils.isNotBlank(filter.getSearchWord())){
            query.append(" and (obj.branch like '"+ StringUtils.getLikewords(filter.getSearchWord()) +"' ) ");
        }
        query.append(" and obj.branch != '' group by branch");
        List<BnpUser> list =  query.listResult(filter);
        return list;
    }

    public List<String> getBranchInAllUser(){
        StringBuffer str = new StringBuffer();
        str.append(" select branch as branch from t_bnp_user group by branch");
        SQLQuery query = getSession().createSQLQuery(str.toString());
        query.setResultTransformer(Transformers.aliasToBean(BnpUser.class));
        List<BnpUser> list = query.list();
        List<String> result = new ArrayList<>();
        for(BnpUser p : list){
            if(StringUtils.isNotBlank(p.getBranch())){
                result.add(p.getBranch());
            }
        }

        //用户按名字排序
        Collections.sort(result, new Comparator<String>() {
            @Override
            public int compare(String o1, String o2) {
                //注意:按照中文拼音排序要进行下面的设置
                Comparator<Object> com = Collator.getInstance(Locale.CHINA);
                return ((Collator) com).compare(o1, o2);
            }
        });

        return result;
    }

}
